#include "stdafx.h"
#include "myDB.h"
#include "ODBCINST.H"
#include <fstream>
#include <sstream>
#include <string>
using namespace std;
myDB myDB::db;
bool myDB::checkIsObjExist(string checkStr)
{
	CRecordset dbRecord(&dbCon);
	string cmd = checkStr;
	dbRecord.Open(CRecordset::forwardOnly, CString(cmd.c_str()));
	int cnt = dbRecord.GetRecordCount();
	if (cnt <= 0)
	{
		return false;
	}
	else
	{
		return true;
	}
}
void myDB::initDB()
{
	configODBC();
	dbCon.OpenEx(L"DSN=autometer", CDatabase::noOdbcDialog);
	if(!checkIsObjExist(checkDBStr))
	{
		dbCon.ExecuteSQL(creatDBStr);
	}
	dbCon.ExecuteSQL(useDBStr);
	if (!checkIsObjExist(checkTableAccountTStr))
	{
		dbCon.ExecuteSQL(useDBStr);
		dbCon.ExecuteSQL(L"create table accountT(name varchar(255) primary key, password varchar(255), authright varchar(255));");
		dbCon.ExecuteSQL(L"insert into accountT values('root', 'root', 'admin');");
	}
	if (!checkIsObjExist(checkTableRecordTStr))
	{
		dbCon.ExecuteSQL(L"create table recordT(rectime varchar(255) primary key, projectname varchar(255),itemSn varchar(255),tester varchar(255),verifier VARCHAR(255), testsetId VARCHAR(255),isrevert VARCHAR(255), result VARCHAR(255), rec varchar(8000));");
	}
}

//todo server name can be configed 
void myDB::configODBC()//not use yet for sql server
{
	//get db local server name
//	string tmp = "DSN=autometer\0 SERVER=DESKTOP-H0TGLD0\0 Trusted_Connection=yes\0";
	WCHAR tmp[250] = L"DSN=autometer\\0 Trusted_Connection=yes\\0 SERVER=DESKTOP-H0TGLD0\\0";
	//tmp[48] = '#';
	if (!SQLConfigDataSource(NULL, ODBC_ADD_DSN, L"SQL Server", (L"DSN=autometer\0 Trusted_Connection=yes\0 SERVER=localhost\0")))
	{
		AfxMessageBox(L"register odbc source fail");
	}
	return;
}
string myDB::getRootPwd()
{
	dbCon.ExecuteSQL(useDBStr);
	CRecordset dbRecord(&dbCon);
	string cmd = "select * from accountT where name = 'root';";
	dbRecord.Open(CRecordset::forwardOnly, CString(cmd.c_str()));
	int cnt = dbRecord.GetRecordCount();
	if (cnt <= 0)
	{
		AfxMessageBox(L"system database error: no root account!");
		return "";
	}
	else
	{
		CDBVariant varValue;
		dbRecord.GetFieldValue(1, varValue);
		string pwd = (varValue.m_pstringA->GetBuffer());
		return pwd;
	}
	dbRecord.Close();
}
bool myDB::isUserExist(string strName)
{
	dbCon.ExecuteSQL(useDBStr);
	CRecordset dbRecord(&dbCon);
	string cmd = "select * from accountT where name =";
	cmd += "'";
	cmd += strName;
	cmd += "';";
	dbRecord.Open(CRecordset::forwardOnly, CString(cmd.c_str()));
	int cnt = dbRecord.GetRecordCount();
	if (cnt <= 0)
	{
		return false;
	}
	else
	{
		return true;
	}
}
int myDB::getAuthRight(string strName, string strPwd)
{
	dbCon.ExecuteSQL(useDBStr);
	CRecordset dbRecord(&dbCon);
	string cmd = "select * from accountT where name =";
	cmd += "'";
	cmd += strName;
	cmd += "' and password='";
	cmd += strPwd;
	cmd += "';";
	dbRecord.Open(CRecordset::forwardOnly, CString(cmd.c_str()));
	int cnt = dbRecord.GetRecordCount();
	if (cnt <= 0)
	{
		return -1;
	}
	else
	{
		CDBVariant varValue;
		dbRecord.GetFieldValue(2, varValue);
		string strAuthRight = (varValue.m_pstringA->GetBuffer());
		if (strAuthRight == "admin")
		{
			return 99;
		}
		else
		{
			return 1;
		}
	}

}

void myDB::addAccount(string name, string pwd, string authright)
{
	dbCon.ExecuteSQL(useDBStr);
	string cmd = "insert into accountT (name, password, authright) values('";
	cmd += name;
	cmd += "','";
	cmd += pwd;
	cmd += "','";
	cmd += authright;
	cmd += "');";
	dbCon.ExecuteSQL(CString(cmd.c_str()));
}

int myDB::changePwd(string name, string oldpwd, string newpwd)
{
	dbCon.ExecuteSQL(useDBStr);
	if (getAuthRight(name, oldpwd) < 0)
	{
		//no account, change pwd fail
		return -1;
	}
	else
	{
		string cmd = "update accountT set password ='";
		cmd += newpwd;
		cmd += "' where name =";
		cmd += "'";
		cmd += name;
		cmd += "' and password='";
		cmd += oldpwd;
		cmd += "';";
		dbCon.ExecuteSQL(CString(cmd.c_str()));
		return 1;
	}

}
void myDB::addRecordToDB(string pname, string time, string itemSN, string tester, string verifier, 
	string testsetId, string isRevert, string result, string rec)
{
	dbCon.ExecuteSQL(useDBStr);
	string cmd = "insert into recordT (projectname, rectime,itemSN, tester,verifier, testsetId, isRevert, result, rec) values('";
	cmd += pname;
	cmd += "','";
	cmd += time;
	cmd += "','";
	cmd += itemSN;
	cmd += "','";
	cmd += tester;
	cmd += "','";
	cmd += verifier;
	cmd += "','";
	cmd += testsetId;
	cmd += "','";
	cmd += isRevert;
	cmd += "','";
	cmd += result;
	cmd += "','";

	cmd += rec;
	cmd += "');";
	dbCon.ExecuteSQL(CString(cmd.c_str()));
}

string myDB::getDBServer()
{
	ifstream is("db.conf");
	string dbServer;
	is >> dbServer;
	return dbServer;
	is.close();
}
